import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao{
	public void createTable(String company){
		DBConnection dbc = null;
		Connection conn = null;
		
		if(company == "CompanyA"){
			dbc = new CompanyADBConnection();
		}else if(company == "CompanyB"){
			dbc = new CompanyBDBConnection();
		}else if(company == "CompanyC"){
			dbc = new CompanyCDBConnection();			
		}
		conn = dbc.createConnection();
		
		
		StringBuffer query = null;

		try{
			conn.setAutoCommit(false);
			query = new StringBuffer();
			query.append("CREATE TABLE member(userid VARCHAR(45), ");
			query.append("password VARCHAR(45), name VARCHAR(45))");
			conn.createStatement().executeUpdate(query.toString());
			conn.commit();
			
			System.out.println("----------------- 테이블 생성");
			System.out.println("테이블 생성 완료");
		}catch(SQLException sqle){
			sqle.printStackTrace(System.out);
		}finally{
			try{
				if(conn != null){
					conn.close();
				}
			}catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
	}
		
	public void insertUser(User user, String company){
		DBConnection dbc = null;
		Connection conn = null;
		
		if(company == "CompanyA"){
			dbc = new CompanyADBConnection();
		}else if(company == "CompanyB"){
			dbc = new CompanyBDBConnection();
		}else if(company == "CompanyC"){
			dbc = new CompanyCDBConnection();			
		}
		conn = dbc.createConnection();

		PreparedStatement pstmt = null;
		StringBuffer query = null;
				
		try{
			conn.setAutoCommit(false);
			query = new StringBuffer();
			query.append("INSERT INTO member VALUES(?, ?, ?)");
			pstmt = conn.prepareStatement(query.toString());
			pstmt.clearParameters();
			pstmt.setString(1, user.getUserid());
			pstmt.setString(2, user.getPasssword());
			pstmt.setString(3, user.getName());
			pstmt.executeUpdate();
			
			conn.commit();
			
			pstmt.close();
			
			System.out.println("----------------- 테이블 값 입력");
			System.out.println(user.getUserid()+", 등록 완료");
		}catch(SQLException sqle){
			sqle.printStackTrace(System.out);
		}finally{
			try{
				if(conn != null){
					conn.close();
				}
			}catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}	
	}
	
	public void selectUser(String userid, String company){
		DBConnection dbc = null;
		Connection conn = null;
		
		if(company == "CompanyA"){
			dbc = new CompanyADBConnection();
		}else if(company == "CompanyB"){
			dbc = new CompanyBDBConnection();
		}else if(company == "CompanyC"){
			dbc = new CompanyCDBConnection();			
		}
		conn = dbc.createConnection();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = null;
		
		try{
			query = new StringBuffer();
			query.append("SELECT * FROM member WHERE USERID = ?");
			
			pstmt = conn.prepareStatement(query.toString());
			pstmt.clearParameters();
			pstmt.setString(1, userid);
			rs = pstmt.executeQuery();
			
			System.out.println("----------------- 테이블 값 조회");
			while(rs.next()){
				System.out.print(rs.getString(1)+", ");
				System.out.print(rs.getString(2)+", ");
				System.out.println(rs.getString(3));
			}
									
			rs.close();
			pstmt.close();
		}catch(SQLException sqle){
			sqle.printStackTrace(System.out);
		}finally{
			try{
				if(conn != null){
					conn.close();
				}
			}catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
	}
	
	public void deleteUser(String userid, String company){
		DBConnection dbc = null;
		Connection conn = null;
		
		if(company == "CompanyA"){
			dbc = new CompanyADBConnection();
		}else if(company == "CompanyB"){
			dbc = new CompanyBDBConnection();
		}else if(company == "CompanyC"){
			dbc = new CompanyCDBConnection();			
		}
		conn = dbc.createConnection();
		
		PreparedStatement pstmt = null;
		StringBuffer query = null;

		try{
			conn.setAutoCommit(false);
			query = new StringBuffer();
			query.append("DELETE FROM member WHERE USERID = ?");
			pstmt = conn.prepareStatement(query.toString());
			pstmt.clearParameters();
			pstmt.setString(1, userid);
			pstmt.executeUpdate();
			
			conn.commit();
			
			pstmt.close();
			
			System.out.println("----------------- 테이블 값 삭제");
			System.out.println(userid + ", 삭제 완료");
		}catch(SQLException sqle){
			sqle.printStackTrace(System.out);
		}finally{
			try{
				if(conn != null){
					conn.close();
				}
			}catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
	}
	
	public void updateUser(User user, String company){
		DBConnection dbc = null;
		Connection conn = null;
		
		if(company == "CompanyA"){
			dbc = new CompanyADBConnection();
		}else if(company == "CompanyB"){
			dbc = new CompanyBDBConnection();
		}else if(company == "CompanyC"){
			dbc = new CompanyCDBConnection();			
		}
		conn = dbc.createConnection();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = null;
		
		try{
			conn.setAutoCommit(false);
			query = new StringBuffer();
			query.append("UPDATE member SET PASSWORD = ?, ");
			query.append("NAME = ? WHERE USERID = ?");
			pstmt = conn.prepareStatement(query.toString());
			pstmt.clearParameters();
			pstmt.setString(1, user.getPasssword());
			pstmt.setString(2, user.getName());
			pstmt.setString(3, user.getUserid());
			pstmt.executeUpdate();
			
			conn.commit();
			
			pstmt.close();
						
			System.out.println("----------------- 테이블 값 갱신");
			System.out.println("비번, 이름 갱신 완료");
		}catch(SQLException sqle){
			sqle.printStackTrace(System.out);
		}finally{
			try{
				if(conn != null){
					conn.close();
				}
			}catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
	}
}
